package DAL;

import DatabaseAccess.ConvertDateToString;
import DatabaseAccess.MyConnection;
import DatabaseAccess.SQLHelper;
import java.sql.SQLException;
import java.util.List;
import Entities.TblStaff;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class StaffDAL {

    private ResultSet objResult;
    public List<TblStaff> list;
    MyConnection mycon = new MyConnection();

    public boolean add(TblStaff staff) throws Exception {
        String sql = "{call ProcInsertStaff (?,?,?,?,?,?,?,?,?,?,?)}";
        Connection conn = null;

        int result = 0;
        try {
            conn = mycon.getConnection();
            CallableStatement stmt = conn.prepareCall(sql);
            stmt.setInt(1, staff.getBranchId());
            stmt.setInt(2, staff.getRoleId());
            stmt.setString(3, staff.getUsername());
            stmt.setString(4, staff.getPassword());
            stmt.setString(5, staff.getStaffName());
            ConvertDateToString convert = new ConvertDateToString();
            String birthDay = convert.convertDateToMMddyyyy(staff.getBirthDay());
            stmt.setString(6, birthDay);            
            stmt.setInt(7, staff.getGender()==true?1:0);
            stmt.setString(8, staff.getPhoneNumber());
            stmt.setString(9, staff.getEmail());
            stmt.setString(10, staff.getAddress());
            stmt.setString(11, staff.getStatus()==true?"1":"0");
            result = stmt.executeUpdate();
        } catch (Exception ex) {            
            System.out.println("Error: " + ex.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        return result == 1 ? true : false;
    }

    public boolean update(TblStaff staff) throws Exception {
        String sql = "{call ProcUpdateStaff (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
        Connection conn = null;
        int result = 0;
        try {
            conn = mycon.getConnection();
            CallableStatement stmt = conn.prepareCall(sql);
            stmt.setInt(1, staff.getStaffId());
            stmt.setInt(2, staff.getBranchId());
            stmt.setInt(3, staff.getRoleId());
            stmt.setString(4, staff.getUsername());
            stmt.setString(5, staff.getPassword());
            stmt.setString(6, staff.getStaffName());
            ConvertDateToString convert = new ConvertDateToString();
            String birthDay = convert.convertDateToMMddyyyy(staff.getBirthDay());
            stmt.setString(7, birthDay);
            stmt.setString(8, staff.getGender()==true?"1":"0");
            stmt.setString(9, staff.getPhoneNumber());
            stmt.setString(10, staff.getEmail());
            stmt.setString(11, staff.getAddress());
            stmt.setString(12, staff.getStatus()==true?"1":"0");
            result = stmt.executeUpdate();
        } catch (Exception ex) {
            System.out.println("Error: " + ex.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        return result == 1 ? true : false;
    }

    public boolean delete(TblStaff staff) throws Exception {
        String sql = "{call procDeleteStaff (?)}";
        Connection conn = null;
        int result = 0;
        try {
            conn = mycon.getConnection();
            CallableStatement stmt = conn.prepareCall(sql);
            stmt.setInt(1, staff.getStaffId());            
            result = stmt.executeUpdate();
        } catch (Exception ex) {
            System.out.println("Error: " + ex.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        return result == 1 ? true : false;
    }

    public List<TblStaff> getAll() throws SQLException {
        String sql = "procGetAllStaff";
        List<TblStaff> list = new ArrayList<TblStaff>();
        TblStaff staff = null;
        Connection conn = null;

        try {
            conn = mycon.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                staff=new TblStaff();
                staff.setStaffId(rs.getInt("StaffId"));
                staff.setBranchId(rs.getInt("BranchId"));
                staff.setRoleId(rs.getInt("RoleId"));
                staff.setUsername(rs.getString("Username"));
                staff.setPassword(rs.getString("Password"));
                staff.setStaffName(rs.getString("StaffName"));
                staff.setBirthDay(rs.getDate("BirthDay"));
                staff.setGender(rs.getBoolean("Gender"));
                staff.setPhoneNumber(rs.getString("PhoneNumber"));
                staff.setEmail(rs.getString("Email"));
                staff.setAddress(rs.getString("Address"));
                staff.setStatus(rs.getBoolean("Status"));
                staff.setIsDelete(rs.getBoolean("IsDelete"));
                list.add(staff);
            }
        } catch (Exception ex) {
            System.out.println("Error: " + ex.getMessage());
            return null;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    public TblStaff getById(int id) throws SQLException {
        String sql = "{call procGetStaffById (?)}";
        TblStaff staff = null;
        CallableStatement cstmt = null;
        Connection conn = null;
        try {
            conn = mycon.getConnection();
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1, id);
            ResultSet rs = cstmt.executeQuery();
            while (rs.next()) {
                staff=new TblStaff();
                staff.setStaffId(rs.getInt("StaffId"));
                staff.setBranchId(rs.getInt("BranchId"));
                staff.setRoleId(rs.getInt("RoleId"));
                staff.setUsername(rs.getString("Username"));
                staff.setPassword(rs.getString("Password"));
                staff.setStaffName(rs.getString("StaffName"));
                staff.setBirthDay(rs.getDate("BirthDay"));
                staff.setGender(rs.getBoolean("Gender"));
                staff.setPhoneNumber(rs.getString("PhoneNumber"));
                staff.setEmail(rs.getString("Email"));
                staff.setAddress(rs.getString("Address"));
                staff.setStatus(rs.getBoolean("Status"));
                staff.setIsDelete(rs.getBoolean("IsDelete"));
            }
        } catch (Exception ex) {
            System.out.println("Error: " + ex.getMessage());
            return null;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return staff;
    }

    public TblStaff getStaffByUsername(String _username) {
        TblStaff staff = null;
        String[] params = new String[1];
        params[0] = _username;
        try {
            objResult = SQLHelper.executeQuery("procGetStaffByUsername", params);
            while (objResult.next()) {
               staff=new TblStaff();
                staff.setStaffId(objResult.getInt("StaffId"));
                staff.setBranchId(objResult.getInt("BranchId"));
                staff.setRoleId(objResult.getInt("RoleId"));
                staff.setUsername(objResult.getString("Username"));
                staff.setPassword(objResult.getString("Password"));
                staff.setStaffName(objResult.getString("StaffName"));
                staff.setBirthDay(objResult.getDate("BirthDay"));
                staff.setGender(objResult.getBoolean("Gender"));
                staff.setPhoneNumber(objResult.getString("PhoneNumber"));
                staff.setEmail(objResult.getString("Email"));
                staff.setAddress(objResult.getString("Address"));
                staff.setStatus(objResult.getBoolean("Status"));
                staff.setIsDelete(objResult.getBoolean("IsDelete"));
            }

            return staff;
        } catch (Exception ex) {
            System.out.println("Error: " + ex.getMessage());
            return null;
        }
    }
}
